package com.plian.system.service.fr.payrolladjustment;

import com.plian.Tools.*;
import com.plian.Tools.pagedata.PageData;
import com.plian.system.cache.UserCache;
import com.plian.system.common.status.CustomCode;
import com.plian.system.config.exception.MyRuntimeException;
import com.plian.system.constant.sys.FormStatusConstant;
import com.plian.system.constant.sys.FormTypeConstant;
import com.plian.system.constant.yth.YthConstant;
import com.plian.system.dao.fr.gsalaryslip.GsalarySlipDao;
import com.plian.system.dao.fr.payrolladjustment.PayrollAdjustmentDao;
import com.plian.system.dao.pm.property.PropertyBasicsDao;
import com.plian.system.dao.sys.orgzation.OrgzationDao;
import com.plian.system.dao.sys.user.UserDao;
import com.plian.system.entity.sys.ApprovalMessage;
import com.plian.system.model.CommonCode;
import com.plian.system.model.ListResult;
import com.plian.system.model.ObjResult;
import com.plian.system.model.page.Page;
import com.plian.system.mybatis.base.BaseFlowService;
import com.plian.system.service.sys.message.IApprovalMessageService;
import com.plian.system.service.sys.orgzation.OrgzationService;
import com.plian.system.service.wf.FormWorkflowService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.plian.system.constant.sys.ApprovalMessageTypeConstant.GZW_TYPE;
import static com.plian.system.constant.sys.FormStatusConstant.*;
import static com.plian.system.constant.sys.FormTypeConstant.FR_PAYROLL_ADJUSTMENT;

@Slf4j
@Service
public class PayrollAdjustmentService implements BaseFlowService {
    @Autowired
    private PayrollAdjustmentDao payrollAdjustmentDao;
    @Autowired
    private GsalarySlipDao gsalarySlipDao;

    @Autowired
    private UserDao userDao;

    @Autowired
    private OrgzationDao orgzationDao;
    @Autowired
    private OrgzationService orgzationService;

    @Autowired
    private PropertyBasicsDao propertyBasicsDao;

    @Autowired
    private FormWorkflowService formWorkflowService;

    @Autowired
    private IPayrollVersionService payrollVersionService;

    private static Pattern PATTERN = Pattern.compile(".0$");

    private static String VERSION = "version";

    private IApprovalMessageService approvalMessageService;

    /**
     * 保存
     *
     * @param
     * @throws Exception
     */
    public ObjResult savereport(PageData obj) {
        ObjResult objResult;
        try {
            //            保存关联表信息
            PageData pd_link = new PageData();
            List<PageData> old_link = payrollAdjustmentDao.findLinksById(obj);
            if (old_link != null) {
                payrollAdjustmentDao.dellink(obj);
            }
            pd_link.put("linkId", UuidUtil.get32UUID());
            pd_link.put("orgId", obj.getString("orgId"));
            pd_link.put("id", obj.getString("id"));
            pd_link.put("extraInfo", obj.getString("extraInfo"));
            if (Tools.notEmpty(obj.getString("staging"))) {
                pd_link.put("status", "-1");
            } else {
                pd_link.put("status", "0");
            }
            payrollAdjustmentDao.savelink(pd_link);
            List<PageData> l1 = new ArrayList<>();
            for (Object s : obj.keySet()) {
                int i = 1;
                /*取key值为co_的value值*/
                if ("unit".equals(s) || "year".equals(s) || "id".equals(s) || "orgId".equals(s) || "status".equals(s) || "extraInfo".equals(s) || "staging".equals(s)) {
                    continue;
                } else {
                    List<List<String>> l = (List<List<String>>) obj.get(s);
                    for (List<String> p : l) {
                        PageData p1 = new PageData();
                        p1.put("columnId", s);
                        p1.put("orderNumber", i);
                        p1.put("values", String.valueOf(p.get(0)));
                        p1.put("columnValueId", UuidUtil.get32UUID());
                        p1.put("linkId", pd_link.getString("linkId"));
                        p1.put("replyValue", String.valueOf(p.get(1)));
                        l1.add(p1);
                        i++;
                    }
                }
            }
            payrollAdjustmentDao.savevalue(l1);
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }
    /*
     * 保存主表
     *
     * */

    public ObjResult savezb(PageData pd) {
        ObjResult objResult;
        try {
            pd.put("unit", TokenUtil.getTokenOrgId());
            pd.put("UNITNAME", orgzationDao.findbyid(TokenUtil.getTokenOrgId()).getString("name"));
            pd.put("APPLICANTNAME", userDao.findbyid(TokenUtil.getTokenUserId()).getString("name"));
            PageData l = payrollAdjustmentDao.findzb(pd);
            if (l == null || "".equals(l.getString("id"))) {
                pd.put("id", UuidUtil.get32UUID());
                pd.put("status", "0");
                pd.put("formType", FormTypeConstant.FR_PAYROLL_ADJUSTMENT);
                pd.put("canedit", "y");
                payrollAdjustmentDao.savezb(pd);
                objResult = new ObjResult(CommonCode.SUCCESS);
            } else {
                objResult = new ObjResult(CommonCode.EXIST);
                objResult.setMessage("已存在本年度的报表");
            }

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        HashMap<String, Object> processVariable = new HashMap<>();
        //formWorkflowService.startProcessAndSimulate(pd.getString("id"), pd.getString("formType"), processVariable);
        return objResult;
    }

    public List<PageData> findlist(String id, String year, String type, int fillingType) throws Exception {
        return findlist(id, year, type, 999, null, null, null, fillingType);
    }

    public List<PageData> findlist(String id, String year, String type, int summaryCode, int fillingType) throws Exception {
        return findlist(id, year, type, summaryCode, null, null, null, fillingType);
    }

    public List<PageData> findlist(String id, String year, String type, int summaryCode, String checked, int fillingType) throws Exception {
        return findlist(id, year, type, summaryCode, checked, null, null, fillingType);
    }

    public List<PageData> findlist(String id, String year, String type, int summaryCode, String checked, String orgId, int fillingType) throws Exception {
        return findlist(id, year, type, summaryCode, checked, orgId, null, fillingType);
    }

    /*
     * 列表界面查询
     *
     * */
    public List<PageData> findlist(String id, String year, String type, int summaryCode, String checked, String orgId, String difference, int fillingType) throws Exception {
        PageData pd = new PageData();
        pd.put("unit", id);
        if (!Tools.isEmpty(year)) {
            pd.put("year", year);
        }
        if (type != null && "0".equals(type)) {
            type = "re.fhTableTypeCode != '6'";
            pd.put("formType", type);
        } else if (type != null && "6".equals(type)) {
            type = "re.fhTableTypeCode = '6'";
            pd.put("formType", type);
        }
        if (summaryCode != 999) {
            String summary = summaryCode + "";
            pd.put("summaryCode", summary);
        }
        if (!Tools.isEmpty(checked)) {
            pd.put("checked", checked);
        }
        if (!Tools.isEmpty(orgId)) {
            pd.put("orgId", orgId);
        }
        if (!Tools.isEmpty(difference)) {
            pd.put("isDifference", difference);
        }
        pd.put("fillingType", fillingType);
        Page page = new Page(false, pd);
        List<PageData> valueList = payrollAdjustmentDao.list_bb(page);
        return valueList;

    }
    public List<PageData> getGsalaryChild(PageData pd, List<PageData> orgList) throws Exception {
        List<PageData> tree = new ArrayList<>();
        Integer fillingType = pd.getInteger("fillingType");
        List<PageData> list = new ArrayList<>();
        for (PageData org : orgList){
            List<PageData> orgChildren = (List<PageData>) org.get("children");
            List<PageData> parentList = new ArrayList<>();
//            if (CollectionUtil.isNotEmpty(orgChildren)) {
            String typeCode = org.getString("typeCode");
            if (StringUtil.isBlank(typeCode)){
                continue;
            }
            switch (typeCode){
                case "0":
                    if (Tools.isEmpty(pd.getString("year"))) {
                        list = findlist(org.getString("id"), "", "6", fillingType);
                    } else {
                        list = findlist(org.getString("id"), pd.getString("year"), "6", fillingType);
                    }
                    if (list.size() == 0) {
                        PageData pdd = new PageData();
                        pdd.put("orgName", org.getString("name"));
                        pdd.put("unit", org.getString("name"));
                        pdd.put("fhTableTypeCode", "6");
                        pdd.put("year", pd.getString("year"));
                        pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                        list.add(pdd);
                    }
                    for (PageData p : list){
                        List<PageData> childList = new ArrayList<>();
                        //遍历当前单位的下属子企业
                        List<PageData> childs = new ArrayList<>();
                        childs = findlist(org.getString("id"), p.getString("year"), "0", fillingType);
                        if (childs.size() > 0) {
                            for (PageData child : childs) {
                                childList.add(child);
                            }
                        } else {
                            PageData pdd = new PageData();
                            pdd.put("orgName", org.getString("name"));
                            pdd.put("unit", org.getString("name"));
                            pdd.put("year", pd.getString("year"));
                            pdd.put("fhTableTypeCode", "0");
                            pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                            childList.add(pdd);
                        }
                        pd.put("year", p.getString("year"));
                        childList.addAll(getGsalaryChild(pd, orgChildren));
                        p.put("children", childList);
                    }
                    tree.addAll(list);
                    break;
                case "1":
                    list = findlist(org.getString("id"), "", "0", fillingType);
                    if (CollectionUtil.isEmpty(list)){
                        PageData pdd = new PageData();
                        pdd.put("orgName", org.getString("name"));
                        pdd.put("unit", org.getString("name"));
                        pdd.put("fhTableTypeCode", "0");
                        pdd.put("year", pd.getString("year"));
                        pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                    }
                    tree.addAll(list);
                    break;
                case "2":
                    list = findlist(org.getString("id"), "", "0", fillingType);
                    if (CollectionUtil.isEmpty(list)){
                        PageData pdd = new PageData();
                        pdd.put("orgName", org.getString("name"));
                        pdd.put("unit", org.getString("name"));
                        pdd.put("fhTableTypeCode", "0");
                        pdd.put("year", pd.getString("year"));
                        pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                    }
                    tree.addAll(list);
                    break;
                case "3":
                    orgzationService.getParentList(org.getString("parentId"), parentList);
                    int summaryCode = parentList.size();
                    pd.put("id", org.getString("id"));
                    pd.put("typeCode", 2);
                    List<PageData> typeOrgs = orgzationDao.findorgbyidandtype(pd);
                    List<PageData> delOrgs = new ArrayList<>();
                    for (PageData orgType:typeOrgs){
                        for (PageData child:orgChildren){
                            if (child.getString("id").equals(orgType.getString("id"))){
                                delOrgs.add(child);
                            }
                        }
                    }
                    orgChildren.removeAll(delOrgs);
                    if (Tools.isEmpty(pd.getString("year"))) {
                        list = findlist(org.getString("id"), "", "6", summaryCode - 1, fillingType);
                    } else {
                        list = findlist(org.getString("id"), pd.getString("year"), "6", summaryCode - 1,fillingType);
                    }
                    if (list.size() == 0) {
                        PageData pdd = new PageData();
                        pdd.put("unit", org.getString("name"));
                        pdd.put("orgName", org.getString("name"));
                        pdd.put("year", pd.getString("year"));
                        pdd.put("fhTableTypeCode", "6");
                        pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                        list.add(pdd);
                    }
                    for (PageData p : list) {
                        List<PageData> childList = new ArrayList<>();
                        //遍历当前单位的下属子企业
                        List<PageData> childs = new ArrayList<>();
                        childs = findlist(org.getString("id"), p.getString("year"), "", summaryCode);
                        if (childs.size() > 0) {
                            for (PageData child : childs) {
                                if ("6".equals(child.getString("fhTableTypeCode"))) {
                                    List<PageData> summarySmall = findlist(org.getString("id"), p.getString("year"), "0", summaryCode + 1, fillingType);
                                    for (PageData branch : typeOrgs) {
                                        List<PageData> branchOrgs = findlist(branch.getString("id"), p.getString("year"), "0", summaryCode + 1, fillingType);
                                        if (branchOrgs.size()>0){
                                            summarySmall.addAll(branchOrgs);
                                        }else {
                                            PageData pdd = new PageData();
                                            pdd.put("unit", branch.getString("name"));
                                            pdd.put("year", pd.getString("year"));
                                            pdd.put("orgName", branch.getString("name"));
                                            pdd.put("fhTableTypeCode", "0");
                                            pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                                            summarySmall.add(pdd);
                                        }

                                    }
                                    for (PageData branchpd : summarySmall) {
                                        if ("1".equals(branchpd.getString("fhTableTypeCode"))) {
                                            branchpd.put("isSmallSummary", 1);
                                        }
                                    }
                                    child.put("children", summarySmall);
                                    child.put("isSmallSummary", 1);
                                }
                                childList.add(child);
                            }
                        } else {
                            PageData pdd = new PageData();
                            pdd.put("unit", org.getString("name"));
                            pdd.put("orgName", org.getString("name"));
                            pdd.put("fhTableTypeCode", "6");
                            pdd.put("year", pd.getString("year"));
                            pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                            childList.add(pdd);
                        }
                        pd.put("year", p.getString("year"));
                        childList.addAll(getGsalaryChild(pd, orgChildren));
                        p.put("children", childList);
                    }
                    tree.addAll(list);
                    break;
                default:
                    break;
            }
        }
        return tree;
    }

    /**
     * 递归，建立树形结构
     *
     * @param pd
     * @return
     */
    public List<PageData> getGsalaryChlid(PageData pd) throws Exception {
        if (Tools.isEmpty(pd.getString("unit"))) {
            pd.put("unit", pd.getString("id"));
        }
        PageData orgpd = orgzationDao.findbyid(pd.getString("unit"));
        List<PageData> parentList = new ArrayList<>();
        orgzationService.getParentList(orgpd.getString("parentId"), parentList);
        int summaryCode = parentList.size();
        List<PageData> list = new ArrayList<>();
        List<PageData> orgs = orgzationDao.findchildbyid(pd.getString("unit"));
        int fillingType = pd.getInteger("fillingType");
        PageData unitOrg = orgzationDao.findbyid(pd.getString("unit"));
        switch (unitOrg.getString("typeCode")) {
            case "0":
                if (orgs.size() > 1) {
                    if (Tools.isEmpty(pd.getString("year"))) {
                        list = findlist(pd.getString("unit"), "", "6", fillingType);
                    } else {
                        list = findlist(pd.getString("unit"), pd.getString("year"), "6", fillingType);
                    }
                    if (list.size() == 0) {
                        PageData pdd = new PageData();
                        pdd.put("orgName", unitOrg.getString("name"));
                        pdd.put("unit", unitOrg.getString("name"));
                        pdd.put("fhTableTypeCode", "6");
                        pdd.put("year", pd.getString("year"));
                        pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                        pdd.put("fillingType", fillingType);
                        list.add(pdd);
                    }
                    //遍历该企业填报的全面预算
                    for (PageData p : list) {
                        List<PageData> childList = new ArrayList<>();
                        //遍历当前单位的下属子企业
                        for (PageData org : orgs) {
                            List<PageData> childs = new ArrayList<>();
                            if (org.getString("id").equals(pd.getString("unit"))) {
                                childs = findlist(org.getString("id"), p.getString("year"), "0", fillingType);
                                if (CollectionUtil.isEmpty(childs)){
                                    PageData pdd = new PageData();
                                    pdd.put("orgName", org.getString("name"));
                                    pdd.put("unit", org.getString("name"));
                                    pdd.put("fhTableTypeCode", "0");
                                    pdd.put("year", pd.getString("year"));
                                    pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                                    pdd.put("fillingType", fillingType);
                                    childs.add(pdd);
                                }
                                for (PageData child : childs) {
                                    childList.add(child);
                                }
                            } else {
                                List<PageData> orgchilds = orgzationService.getChlidList(org.getString("id"));
                                if (orgchilds.size() > 1) {
                                    childs = findlist(org.getString("id"), p.getString("year"), "6", fillingType);
                                    if (CollectionUtil.isEmpty(childs)){
                                        PageData pdd = new PageData();
                                        pdd.put("orgName", org.getString("name"));
                                        pdd.put("unit", org.getString("name"));
                                        pdd.put("fhTableTypeCode", "6");
                                        pdd.put("year", pd.getString("year"));
                                        pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                                        pdd.put("fillingType", fillingType);
                                        childs.add(pdd);
                                    }
                                } else {
                                    childs = findlist(org.getString("id"), p.getString("year"), "0", fillingType);
                                    if (CollectionUtil.isEmpty(childs)){
                                        PageData pdd = new PageData();
                                        pdd.put("orgName", org.getString("name"));
                                        pdd.put("unit", org.getString("name"));
                                        pdd.put("fhTableTypeCode", "0");
                                        pdd.put("year", pd.getString("year"));
                                        pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                                        pdd.put("fillingType", fillingType);
                                        childs.add(pdd);
                                    }
                                }
                                for (PageData child : childs) {
                                    org.put("year", child.getString("year"));
                                    org.put("fillingType", child.getInteger("fillingType"));
                                    if (orgchilds.size() > 1) {
                                        child.putAll(getGsalaryChlid(org).get(0));
                                    }
                                    childList.add(child);
                                }

                            }
                        }
                        p.put("children", childList);
                    }
                }
                break;
            case "1":
                list = findlist(pd.getString("unit"), "", "0", fillingType);
                if (CollectionUtil.isEmpty(list)){
                    PageData pdd = new PageData();
                    pdd.put("orgName", unitOrg.getString("name"));
                    pdd.put("unit", unitOrg.getString("name"));
                    pdd.put("fhTableTypeCode", "0");
                    pdd.put("year", pd.getString("year"));
                    pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                    pdd.put("fillingType", fillingType);
                    list.add(pdd);
                }
                break;
            case "2":
                list = findlist(pd.getString("unit"), "", "0", fillingType);
                if (CollectionUtil.isEmpty(list)){
                    PageData pdd = new PageData();
                    pdd.put("orgName", unitOrg.getString("name"));
                    pdd.put("unit", unitOrg.getString("name"));
                    pdd.put("fhTableTypeCode", "0");
                    pdd.put("year", pd.getString("year"));
                    pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                    pdd.put("fillingType", fillingType);
                    list.add(pdd);
                }
                break;
            case "3":
                pd.put("id", pd.getString("unit"));
                pd.put("typeCode", "2");
                List<PageData> typeOrgs = orgzationDao.findorgbyidandtype(pd);
                if (Tools.isEmpty(pd.getString("year"))) {
                    list = findlist(pd.getString("unit"), "", "6", summaryCode - 1, fillingType);
                } else {
                    list = findlist(pd.getString("unit"), pd.getString("year"), "6", summaryCode - 1, fillingType);
                }
                if (CollectionUtil.isEmpty(list)){
                    PageData pdd = new PageData();
                    pdd.put("orgName", unitOrg.getString("name"));
                    pdd.put("unit", unitOrg.getString("name"));
                    pdd.put("fhTableTypeCode", "6");
                    pdd.put("year", pd.getString("year"));
                    pdd.put("status", String.valueOf(FormStatusConstant.NOFILLING));
                    pdd.put("fillingType", fillingType);
                    list.add(pdd);
                }

                //遍历该企业填报的全面预算
                for (PageData p : list) {
                    List<PageData> childList = new ArrayList<>();
                    //遍历当前单位的下属子企业
                    for (PageData org : orgs) {
                        List<PageData> childs = new ArrayList<>();
                        if (org.getString("id").equals(pd.getString("unit"))) {
                            childs = findlist(org.getString("id"), p.getString("year"), "", summaryCode, fillingType);
                            for (PageData child : childs) {
                                if ("6".equals(child.getString("fhTableTypeCode"))) {
                                    List<PageData> summarySmall = findlist(org.getString("id"), p.getString("year"), "0", summaryCode + 1, fillingType);
                                    for (PageData branch : typeOrgs) {
                                        List<PageData> branchOrgs = findlist(branch.getString("id"), p.getString("year"), "0", summaryCode + 1, fillingType);
                                        summarySmall.addAll(branchOrgs);
                                    }
                                    for (PageData branchpd : summarySmall) {
                                        if ("1".equals(branchpd.getString("fhTableTypeCode"))) {
                                            branchpd.put("isSmallSummary", 1);
                                        }
                                    }
                                    child.put("children", summarySmall);
                                    child.put("isSmallSummary", 1);
                                }
                                childList.add(child);
                            }

                        } else {

                            List<PageData> orgchilds = orgzationService.getChlidList(org.getString("id"));
                            if (orgchilds.size() > 1) {
                                childs = findlist(org.getString("id"), p.getString("year"), "6", summaryCode, fillingType);
                            } else {
                                childs = findlist(org.getString("id"), p.getString("year"), "0", summaryCode, fillingType);
                            }
                            for (PageData child : childs) {
                                org.put("year", child.getString("year"));
                                if (orgchilds.size() > 1) {
                                    child.putAll(getGsalaryChlid(org).get(0));
                                }
                                childList.add(child);
                            }
                        }
                    }
                    p.put("children", childList);
                }
                break;
            default:
                break;
        }
        return list;
    }

    /*
     * 列表界面
     * */
    public ListResult<PageData> listpage(PageData pd) {
        try {
            Page page = new Page(true, pd);
            List<PageData> orgList = orgzationService.getUnitSubordinate().getList();
            if (CollectionUtil.isEmpty(orgList)) {
                return new ListResult<>(CustomCode.builder().message("企业树初始化失败").code(1999).success(false).build());
            }
            List<PageData> list = getGsalaryChild(pd, orgList);
            int pageTotal = (page.getPageNumber() - 1) * page.getPageSize();
            int pageTotal1 = page.getPageNumber() * page.getPageSize();
            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            if (list.size() < pageTotal1) {
                Result.setList(list.subList(pageTotal, list.size()));
            } else {
                Result.setList(list.subList(pageTotal, pageTotal1));
            }
            Result.setTotal(list.size());
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /*
     * 列表界面
     * */
    public ListResult<PageData> detaillist(PageData pd) {
        try {
            PageData zb = payrollAdjustmentDao.findZbById(pd);
            PageData org = orgzationDao.findbyid(zb.getString("unit"));
            int summaryCode = new Integer(zb.getString("summaryCode"));
            pd.putAll(zb);
            PageData pdorg = payrollAdjustmentDao.findOrgMes(pd);
            List<String> ids = new ArrayList<>();
            List<PageData> childs = orgzationDao.findchildbyid(zb.getString("unit"));
            int fillingType = pd.getInteger("fillingType");
            List<PageData> parentList = findlist(zb.getString("unit"), zb.getString("year"), "6", summaryCode - 1, null, pd.getString("orgId"), fillingType);
            for (PageData child : childs) {
                List<PageData> childlist = new ArrayList<>();
                if (zb.getString("unit").equals(child.getString("id"))) {
                    childlist = findlist(child.getString("id"), zb.getString("year"), null, summaryCode + 1, null, pd.getString("orgId"), fillingType);
                } else {
                    if ("1".equals(child.getString("typeCode")) || "2".equals(child.getString("typeCode"))) {
                        childlist = findlist(child.getString("id"), zb.getString("year"), "0", summaryCode + 1, null, pd.getString("orgId"), fillingType);
                    } else if (!"3".equals(child.getString("typeCode"))) {
                        childlist = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode + 1, fillingType);
                    }else {
                        childlist = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode + 1, fillingType);

                    }

                }
                for (PageData pdd : childlist) {
                    ids.add(pdd.getString("id"));
                }
            }
            pd.put("ids", ids);
            List<PageData> list;
            if (ids.size() > 0) {
                list = payrollAdjustmentDao.listDeatil(pd);
                if ("3".equals(org.getString("typeCode"))){
                    for (PageData p : list) {
                        if (("6".equals(p.getString("fhTableTypeCode")) || (("1".equals(p.getString("fhTableTypeCode")) && parentList.size()>0)) && p.getString("unit").equals(zb.getString("unit")))) {
                            p.put("isSmallSummary", 1);
                        }
                    }
                }
            } else {
                list = null;
            }

            ListResult<PageData> Result;
            if ("0".equals(pdorg.getString("isadd"))) {
                Result = new ListResult<>(CommonCode.FAIL);
                Result.setMessage("该表不进行汇总，无法查看明细");
            } else {
                Result = new ListResult<>(CommonCode.SUCCESS);
                Result.setList(list);
            }

            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /*
     * 列表界面
     * */
    public ListResult<PageData> findGsalaryValue(PageData pd) {
        try {
            PageData pd_column = new PageData();
            PageData ZB = payrollAdjustmentDao.findZbById(pd);
            pd_column.put("orgId", pd.getString("orgId"));
            PageData form = payrollAdjustmentDao.findorgname(pd_column);
            pd_column.put("orgId", form.getString("gsalaryOrgId"));
            ZB.put("orgId", form.getString("gsalaryOrgId"));
            List<PageData> colist = gsalarySlipDao.findcolumn(pd_column);
            String value = "";
            for (int i = 0; i < colist.size(); i++) {
                PageData p = colist.get(i);
                String v = p.getString("columnId");
                if ("".equals(value)) {
                    value += " max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                } else {
                    value += " ,max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                }
            }
            ZB.put("vaues", value);
            List<PageData> valuelist = gsalarySlipDao.findValueByZb(ZB);
            if (valuelist.size() > 0) {
                ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
                Result.setList(valuelist);
                return Result;
            } else {
                ListResult<PageData> Result = new ListResult<>(CommonCode.FAIL);
                Result.setMessage("此年度未填报相关数据");
                return Result;
            }

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /**
     * 封装list方法
     *
     * @return
     */
    public List<PageData> alist(PageData pd) throws Exception {
        PageData pd_column = new PageData();
        pd_column.put("orgId", pd.getString("orgId"));
        List<PageData> colist = payrollAdjustmentDao.findcolumn(pd_column);
        String value = "";
        for (int i = 0; i < colist.size(); i++) {
            PageData p = colist.get(i);
            String v = p.getString("columnId");
            if ("".equals(value)) {
                value += " max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
            } else {
                value += " ,max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
            }
        }
        pd.put("vaues", value);
        List<PageData> valuelist = payrollAdjustmentDao.findvalue(pd);
        return valuelist;
    }

    /**
     * 查询值表
     */
    public ListResult<PageData> valuelist(PageData pd) {
        try {
            String extra = "";
            if (!Tools.isEmpty(payrollAdjustmentDao.findExtraById(pd))) {
                extra = payrollAdjustmentDao.findExtraById(pd);
            }
            PageData zb = payrollAdjustmentDao.findZbById(pd);
            int fillingType = zb.getInteger("fillingType");
            String msg = "";
//            判断是否通过汇总按钮
            if (Tools.notEmpty(pd.getString("isSummary")) || Tools.notEmpty(pd.getString("isDifference"))) {
                String checkedIds = "";
                List<String> checkedIdslist = (List<String>) pd.get("checkedIds");
                if (checkedIdslist.size() > 0) {
                    for (String id : checkedIdslist) {
                        if (Tools.isEmpty(checkedIds)) {
                            checkedIds += "'" + id + "'";
                        } else {
                            checkedIds += ",'" + id + "'";
                        }
                    }

                    pd.put("ids", checkedIds);
                }
            }
            List<PageData> valuelist = new ArrayList<>();
            if (Tools.isEmpty(pd.getString("isSummary"))) {
                if (Tools.notEmpty(pd.getString("isDifference"))) {
                    PageData tableMes = payrollAdjustmentDao.findOrgMes(pd);
                    if ("1".equals(tableMes.getString("isadd"))) {
                        Integer summaryCode = new Integer(zb.getString("summaryCode"));
                        List<PageData> parentList = findlist(zb.getString("unit"), zb.getString("year"), "6", summaryCode - 1, fillingType);
                        if (parentList != null) {
                            pd.put("addid", parentList.get(0).getString("id"));
                            valuelist = differenceList(pd);
                        } else {
                            ListResult lr = new ListResult(CommonCode.FAIL);
                            lr.setMessage("请先在集团汇总表录入本表数据");
                            return lr;

                        }
                    } else {
                        valuelist = alist(pd);
                    }
                } else {
                    valuelist = alist(pd);
                }
            } else {
//                获取表格是否需要合并
                PageData tableMes = payrollAdjustmentDao.findOrgMes(pd);
                if ("1".equals(tableMes.getString("isadd")) && Tools.notEmpty(pd.getString("isSummary"))) {
//                    可增行报表合并
//                    if (pd.getString("orgId").equals("ta_07-01") || pd.getString("orgId").equals("ta_07-02")) {
//                        String title = "";
////            分别查询“自营项目（主业项目）”、“自营项目（培育业务项目）”、“自营项目（非主业项目）”和“政府性项目”下的项目
//                        for (int i = 0; i <= 4; i++) {
//                            pd.put("lineNumber", i);
//                            List<PageData> tablelist = addoverlist(pd);
//                            int j = 1;
//                            for (PageData p : tablelist) {
//                                if (i > 0) {
//                                    p.put("c0", i + "." + j);
//                                    j++;
//                                }
//                                valuelist.add(p);
//                            }
//                            if (i > 0 && i <= 4) {
//                                PageData pdtitle = new PageData();
//                                pdtitle.putAll(pd);
//                                title = "title";
//                                pdtitle.put("linetype", title);
//                                List<PageData> titlelist = addoverlist(pdtitle);
//                                valuelist.add(titlelist.get(0));
//                            }
//                        }
//                    } else if (pd.getString("orgId").equals("ta_13") || pd.getString("orgId").equals("ta_18")) {
//                        for (int i = 0; i < 3; i++) {
//                            pd.put("lineNumber", i);
//                            List<PageData> tablelist = addoverlist(pd);
//                            int j = 1;
//                            for (PageData p : tablelist) {
//                                if (i > 1) {
//                                    p.put("c0", j);
//                                    j++;
//                                }
//                                valuelist.add(p);
//                            }
//                        }
//
//                    } else if (pd.getString("orgId").equals("ta_14")) {
//                        String title = "";
////            分别查询“自营项目（主业项目）”、“自营项目（培育业务项目）”、“自营项目（非主业项目）”和“政府性项目”下的项目
//                        for (int i = 0; i <= 4; i++) {
//                            pd.put("lineNumber", i);
//                            List<PageData> tablelist = addoverlist(pd);
//                            PageData pdtitle = new PageData();
//                            pdtitle.putAll(pd);
//                            title = "title";
//                            pdtitle.put("linetype", title);
//                            List<PageData> titlelist = addoverlist(pdtitle);
//
//                            int j = 1;
//                            for (PageData p : tablelist) {
//                                if (i > 0) {
//                                    if (i == 1) {
//                                        p.put("c0", i + ".1." + j);
//                                    } else if (i == 2) {
//                                        p.put("c0", (i - 1) + ".2." + j);
//                                    } else {
//                                        p.put("c0", (i - 1) + "." + j);
//                                    }
//                                    j++;
//                                }
//                                valuelist.add(p);
//                            }
//                            for (PageData p : titlelist) {
//                                valuelist.add(p);
//                            }
//                        }
//                    } else {
                    valuelist = addlist(pd);
//                    }

                } else {
                    valuelist = alist(pd);
                }
            }


            ListResult<PageData> Result = new ListResult<>(CommonCode.SUCCESS);
            Result.setList(valuelist);
//            if (!Tools.isEmpty(extra)){
            Result.setRemark(extra);
//            }
            return Result;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return new ListResult<>(CommonCode.ERROR);
        }
    }

    /**
     * 封装addlist方法
     *
     * @return
     */
    public List<PageData> addlist(PageData pd) throws Exception {
        PageData pd_column = new PageData();
        pd_column.put("orgId", pd.getString("orgId"));
        List<PageData> colist = payrollAdjustmentDao.findcolumn(pd_column);
        String value = "";
        String addval = "";
        for (int i = 0; i < colist.size(); i++) {
            PageData p = colist.get(i);
            String v = p.getString("columnId");
            int isNumValue = new Integer(p.getString("isNumValue"));
            if (isNumValue == 1) {
                value += " ,sum(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
            } else {
                if (Tools.isEmpty(value)) {
                    value += " max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                } else {
                    value += " ,max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                }

            }
        }
        pd.put("vaues", value);
        pd.put("orgCode", pd.getString("unit"));
        List<PageData> valuelist = payrollAdjustmentDao.addvalue(pd);
        return valuelist;
    }

    /**
     * 封装differenceList方法
     *
     * @return
     */
    public List<PageData> differenceList(PageData pd) throws Exception {
        PageData pd_column = new PageData();
        pd_column.put("orgId", pd.getString("orgId"));
        List<PageData> colist = payrollAdjustmentDao.findcolumn(pd_column);
        String value = "";
        String differenceval = "";
        for (int i = 0; i < colist.size(); i++) {
            PageData p = colist.get(i);
            String v = p.getString("columnId");
            int isNumValue = new Integer(p.getString("isNumValue"));
            if (isNumValue == 1) {
                value += " ,if(sum(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END)='',max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END),sum(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END)) AS " + "c" + i;
                differenceval += ",sum( a.c" + i + " - b.c" + i + " ) AS c" + i;
            } else {
                if (Tools.isEmpty(value)) {
                    value += " max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                    differenceval += "a.c" + i;
                } else {
                    value += " ,max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
                    differenceval += ",a.c" + i;
                }

            }
        }
        pd.put("vaues", value);
        pd.put("differenceval", differenceval);
        pd.put("orgCode", pd.getString("unit"));
        List<PageData> valuelist = payrollAdjustmentDao.differencevalue(pd);
        return valuelist;
    }

    /**
     * 拼接可变行查询语句
     *
     * @return
     */
    public List<PageData> addoverlist(PageData pd) throws Exception {
        PageData pd_column = new PageData();
        pd_column.put("orgId", pd.getString("orgId"));
        List<PageData> colist = payrollAdjustmentDao.findcolumn(pd_column);
//        外层select查询字段语句
        String selectval = "a.*";
        String addval = "";
//        内层select查询字段语句
        String value = "";
//        内层的筛选条件
        String whereval1 = "";
//        外层筛选条件
        String whereval2 = "";
//        内层group by 语句
        String groupval = "";
        for (int i = 0; i < colist.size(); i++) {
            PageData p = colist.get(i);
            String v = p.getString("columnId");
            int isNumValue = new Integer(p.getString("isNumValue"));
            if ("".equals(value)) {
                value += " max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;

            } else {
                value += " ,max(CASE c.columnId WHEN '" + v + "' THEN v.`values` ELSE '' END) AS " + "c" + i;
            }
            switch (isNumValue) {
                case 0:
                    if ("".equals(addval)) {
                        addval += "a.c" + i;
                    } else {
                        addval += ",a.c" + i;
                    }
                    break;
                case 1:
//                    addval+=",ifnull( sum( a.c"+i+" - b.c"+i+" ), 0 ) AS c"+ i;
                    addval += ",ifnull( sum( a.c" + i + " ),0) AS c" + i;
                    break;
                case 2:
                    break;
                case 4:
                    break;
                default:
                    break;
            }
        }

        if ("ta_07-01".equals(pd.getString("orgId")) || "ta_07-02".equals(pd.getString("orgId"))) {
            if ("0".equals(pd.getString("lineNumber"))) {
                whereval1 = " v.ordernumber < 8";
            } else {
                //判断是否为标题行
                if (pd.getString("linetype") != null) {
                    int i = new Integer(pd.getString("lineNumber"));
                    if (i < 4) {
                        whereval2 = "a.c0 = '" + (i + 1) + "'";
                    } else {
                        if ("ta_07-02".equals(pd.getString("orgId"))) {
                            whereval2 = "a.c0= '合计'";
                        } else {
                            whereval2 = "a.c0= '合   计'";
                        }
                    }
                } else {
                    whereval2 = "a.c0 like '%" + pd.getString("lineNumber") + ".%'";
                    groupval = "v.LINKID";
                }
            }
        } else if ("ta_13".equals(pd.getString("orgId"))) {
            if ("0".equals(pd.getString("lineNumber"))) {
                whereval1 = " v.ordernumber < 6";
            } else if ("1".equals(pd.getString("lineNumber"))) {
//                selectval = addval;
                whereval1 = " v.ordernumber=6";
//                groupval = "v.LINKID";
//                whereval2 = " 1=1 group by a.ordernumber order by ordernumber";
            } else {
                whereval1 = " v.ordernumber>6";
                groupval = "v.LINKID";
            }
        } else if ("ta_14".equals(pd.getString("orgId"))) {
            int i = new Integer(pd.getString("lineNumber"));
            if (i == 0) {
                if (pd.getString("linetype") != null) {
                    whereval2 = "a.c1 = '一、股权资产合计' or a.c1 = '（一）处置参股股权小计'";
                } else {
                    whereval1 = " v.ordernumber < 6";
                }
            } else {
                //判断是否为标题行
                if (pd.getString("linetype") != null) {
                    switch (i) {
                        case 1:
                            whereval2 = "a.c1 = '（二）处置控股股权小计'";
                            break;
                        case 2:
                            whereval2 = "a.c1 = '二、实物资产合计'";
                            break;
                        case 3:
                            whereval2 = "a.c1 = '三、金融资产合计'";
                            break;
                        case 4:
                            whereval2 = "a.c1 = '资产处置总计'";
                            break;
                        default:
                            break;
                    }
                } else {
                    if (i == 1) {
                        whereval2 = "a.c0 like '%" + i + ".1.%'";
                    } else if (i == 2) {
                        whereval2 = "a.c0 like '%" + (i - 1) + ".2.%'";
                    } else {
                        whereval2 = "a.c0 like '%" + (i - 1) + ".%'";
                    }
                    groupval = "v.LINKID";
                }
            }
        } else if ("ta_18".equals(pd.getString("orgId"))) {
            if ("0".equals(pd.getString("lineNumber"))) {
                whereval1 = " v.ordernumber < 8";
            } else if ("1".equals(pd.getString("lineNumber"))) {
                //                selectval = addval;
                whereval2 = " a.c0='合计'";
//                groupval = "v.LINKID";
//                whereval2 = " 1=1 group by a.ordernumber order by ordernumber";
            } else {
                whereval1 = " v.ordernumber>8 and v.ordernumber < 24";
                groupval = "v.LINKID";
            }
        }
        pd.put("selectval", selectval);
        pd.put("vaues", value);
        pd.put("whereval1", whereval1);
        pd.put("whereval2", whereval2);
        pd.put("groupval", groupval);
        pd.put("orgCode", pd.getString("unit"));
        List<PageData> valuelist = payrollAdjustmentDao.overvalue(pd);
        return valuelist;
    }


    /**
     * 删除
     *
     * @throws Exception
     */
    public ObjResult delete(PageData pd) {
        ObjResult objResult;
        try {
            PageData gsalarySlip = payrollAdjustmentDao.findZbById(pd);
            List<String> list = new ArrayList<>();
            if ("6".equals(gsalarySlip.getString("fhTableTypeCode"))){
                list.addAll(payrollAdjustmentDao.findByUnitAndYear(gsalarySlip));
            }else {
                list.add(gsalarySlip.getString("id"));
            }
            for (String id : list) {
                formWorkflowService.deleteProcessInstance(id);
            }
            payrollAdjustmentDao.delete(gsalarySlip);
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /**
     * 初始化报表
     *
     * @param
     * @throws Exception
     */
    public ObjResult originReport(PageData pd) {
        ObjResult objResult;
        try {
            pd.put("unit", TokenUtil.getTokenOrgId());
            if (pd.get("year") == null) {
                Calendar c = Calendar.getInstance();
                int year = c.get(Calendar.YEAR);
                pd.put("year", year);
            }
            PageData org = orgzationDao.findbyid(pd.getString("unit"));
            List<PageData> parentList = new ArrayList<>();
            orgzationService.getParentList(org.getString("parentId"), parentList);
            String formTypeCode[] = {"0", "1", "6"};
            String formTypeCode2[] = {"1", "6"};
            PageData report = new PageData();
            int summaryCode = parentList.size();
            int isExist = 0;
            String tablename = "";
            switch (orgzationDao.findbyid(pd.getString("unit")).getString("typeCode")) {
                case "0":
                    for (int i = 0; i < formTypeCode.length; i++) {
                        pd.put("fhTableTypeCode", formTypeCode[i]);
                        if ("6".equals(formTypeCode[i])) {
                            pd.put("summaryCode", summaryCode - 1);
                        } else {
                            pd.put("summaryCode", summaryCode);
                        }
                        report = payrollAdjustmentDao.findzb(pd);
                        if (report == null) {
                            savezb(pd);
                        } else {
                            if ("6".equals(formTypeCode[i])) {
                                isExist = 1;
                            } else {
                                if ("6".equals(formTypeCode[i])) {
                                    tablename += "“集团合并表”、";
                                    isExist = 1;
                                } else if ("1".equals(formTypeCode[i])) {
                                    tablename += "“差额表”、";
                                    isExist = 1;
                                } else {
                                    tablename += "“单户表”、";
                                    isExist = 1;
                                }
                            }
                        }
                    }
                    break;
                case "1":
                    pd.put("fhTableTypeCode", "0");
                    pd.put("summaryCode", summaryCode - 1);
                    report = payrollAdjustmentDao.findzb(pd);
                    if (report == null) {
                        savezb(pd);
                    } else {
                        isExist = 1;
                    }
                    break;
                case "2":
                    pd.put("fhTableTypeCode", "0");
                    pd.put("summaryCode", summaryCode);
                    report = payrollAdjustmentDao.findzb(pd);
                    if (report == null) {
                        savezb(pd);
                    } else {
                        isExist = 1;
                    }
                    break;
                case "3":
                    for (int i = 0; i < formTypeCode.length; i++) {
                        pd.put("fhTableTypeCode", formTypeCode[i]);
                        if ("6".equals(formTypeCode[i])) {
                            pd.put("summaryCode", summaryCode);
                        } else {
                            pd.put("summaryCode", summaryCode + 1);
                        }
                        report = payrollAdjustmentDao.findzb(pd);
                        if (report == null) {
                            savezb(pd);
                        } else {
                            if ("6".equals(formTypeCode[i])) {
                                tablename += "“集团合并表” ";
                                isExist = 1;
                            } else if ("1".equals(formTypeCode[i])) {
                                tablename += "“差额表” ";
                                isExist = 1;
                            } else {
                                tablename += "“单户表” ";
                                isExist = 1;
                            }
                        }
                    }
                    for (int i = 0; i < formTypeCode2.length; i++) {
                        pd.put("fhTableTypeCode", formTypeCode2[i]);
                        if ("6".equals(formTypeCode2[i])) {
                            pd.put("summaryCode", summaryCode - 1);
                        } else {
                            pd.put("summaryCode", summaryCode);
                        }
                        report = payrollAdjustmentDao.findzb(pd);
                        if (report == null) {
                            savezb(pd);
                        } else {
                            if ("6".equals(formTypeCode[i])) {
                                tablename += "“集团合并表(分公司)” ";
                                isExist = 1;
                            } else if ("1".equals(formTypeCode[i])) {
                                tablename += "“差额表(分公司)” ";
                                isExist = 1;
                            } else {
                                tablename += "“单户表” ";
                                isExist = 1;
                            }
                        }
                    }
                    break;
                default:
                    break;
            }

            if (isExist == 0) {
                objResult = new ObjResult(CommonCode.SUCCESS);
                objResult.setPageData(pd);
            } else {
                objResult = new ObjResult(CommonCode.NORMAL);
                objResult.setMessage("已存在本年度" + tablename + ",其余表新建成功");
            }
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }


    /*
是否通过校验
*/
    public ObjResult updatePassStatus(PageData pd) {
        ObjResult objResult;
        try {
            if (Tools.notEmpty(pd.getString("passed"))) {
                pd.put("isPassed", "1");
                payrollAdjustmentDao.updatePassStatus(pd);
            } else {
                pd.put("isPassed", "0");
                payrollAdjustmentDao.updatePassStatus(pd);
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
保存合并状态
*/
    public ObjResult saveChecked(PageData pd) {
        ObjResult objResult;
        try {

            String uncheckedId = "";
//            更改合并表被选中的状态
            String checkedIds = "";
            List<String> uncheckedIdlist = (List<String>) pd.get("uncheckedIds");
            pd.put("version", payrollVersionService.getVersion(pd.getString("year")));
            List<PageData> orgList = payrollAdjustmentDao.listAllOrg(pd);
            if (uncheckedIdlist.size() > 0) {
                for (String id : uncheckedIdlist) {
                    if (Tools.isEmpty(uncheckedId)) {
                        uncheckedId += "'" + id + "'";
                    } else {
                        uncheckedId += ",'" + id + "'";
                    }
                }
                pd.put("checked", "0");
                pd.put("ids", uncheckedId);

                if (Tools.isEmpty(pd.getString("orgId"))) {
                    payrollAdjustmentDao.changCheck(pd);
                    for (PageData p : orgList) {
                        pd.put("orgId", p.getString("orgId"));
                        payrollAdjustmentDao.changLinkCheck(pd);
                    }

                } else {
                    payrollAdjustmentDao.changLinkCheck(pd);
                }
            }
            String msg = "";
            List<String> checkedIdslist = (List<String>) pd.get("checkedIds");
            if (checkedIdslist.size() > 0) {
                for (String id : checkedIdslist) {
                    if (Tools.isEmpty(checkedIds)) {
                        checkedIds += "'" + id + "'";
                    } else {
                        checkedIds += ",'" + id + "'";
                    }
                    if (Tools.notEmpty(pd.getString("orgId"))) {
                        String status = "";
                        PageData pdorg = new PageData();
                        pdorg.put("id", id);
                        pdorg.put("orgId", pd.getString("orgId"));
                        PageData link = payrollAdjustmentDao.findLinkById(pdorg);
                        if (link != null && Tools.notEmpty(payrollAdjustmentDao.findLinkById(pdorg).getString("status"))) {
                            status = link.getString("status");
                        }
                        if (Tools.notEmpty(pd.getString("orgId")) && "-1".equals(status)) {
                            if (Tools.isEmpty(msg)) {
                                msg += "'" + orgzationDao.findbyid(payrollAdjustmentDao.findZbById(pdorg).getString("unit")).getString("name") + "的" + payrollAdjustmentDao.findorgname(pd).getString("name") + "'";
                            } else {
                                msg += ",'" + orgzationDao.findbyid(payrollAdjustmentDao.findZbById(pdorg).getString("unit")).getString("name") + "的" + payrollAdjustmentDao.findorgname(pd).getString("name") + "'";
                            }
                        }
                    }
                }

                pd.put("checked", "1");
                pd.put("ids", checkedIds);
                if (Tools.isEmpty(pd.getString("orgId"))) {
                    payrollAdjustmentDao.changCheck(pd);
                    for (PageData p : orgList) {
                        pd.put("orgId", p.getString("orgId"));
                        payrollAdjustmentDao.changLinkCheck(pd);
                    }
                } else {
                    payrollAdjustmentDao.changLinkCheck(pd);
                }

            }
            objResult = new ObjResult(CommonCode.SUCCESS);
            if (Tools.notEmpty(msg)) {
                objResult.setMessage(msg + "为暂存");
            } else {
                objResult.setMessage("汇总完成");
            }
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
   保存差额合并状态
   */
    public ObjResult saveDiffChecked(PageData pd) {
        ObjResult objResult;
        try {

            String uncheckedId = "";
//            更改合并表被选中的状态
            String checkedIds = "";
            List<String> uncheckedIdlist = (List<String>) pd.get("uncheckedIds");
            if (uncheckedIdlist.size() > 0) {
                for (String id : uncheckedIdlist) {
                    if (Tools.isEmpty(uncheckedId)) {
                        uncheckedId += "'" + id + "'";
                    } else {
                        uncheckedId += ",'" + id + "'";
                    }
                }
                pd.put("checked", "0");
                pd.put("ids", uncheckedId);
                if (Tools.isEmpty(pd.getString("orgId"))) {
                    payrollAdjustmentDao.changDiffCheck(pd);
                } else {
                    payrollAdjustmentDao.changDiffLinkCheck(pd);
                }
            }
            String msg = "";
            List<String> checkedIdslist = (List<String>) pd.get("checkedIds");
            if (checkedIdslist.size() > 0) {
                for (String id : checkedIdslist) {
                    if (Tools.isEmpty(checkedIds)) {
                        checkedIds += "'" + id + "'";
                    } else {
                        checkedIds += ",'" + id + "'";
                    }
                    if (Tools.notEmpty(pd.getString("orgId"))) {
                        String status = "";
                        PageData pdorg = new PageData();
                        pdorg.put("id", id);
                        pdorg.put("orgId", pd.getString("orgId"));
                        PageData link = payrollAdjustmentDao.findLinkById(pdorg);
                        if (link != null && Tools.notEmpty(payrollAdjustmentDao.findLinkById(pdorg).getString("status"))) {
                            status = link.getString("status");
                        }
                        if (Tools.notEmpty(pd.getString("orgId")) && "-1".equals(status)) {
                            if (Tools.isEmpty(msg)) {
                                msg += "'" + orgzationDao.findbyid(payrollAdjustmentDao.findZbById(pdorg).getString("unit")).getString("name") + "的" + payrollAdjustmentDao.findorgname(pd).getString("name") + "'";
                            } else {
                                msg += ",'" + orgzationDao.findbyid(payrollAdjustmentDao.findZbById(pdorg).getString("unit")).getString("name") + "的" + payrollAdjustmentDao.findorgname(pd).getString("name") + "'";
                            }
                        }
                    }
                }

                pd.put("checked", "1");
                pd.put("ids", checkedIds);
                if (Tools.isEmpty(pd.getString("orgId"))) {
                    payrollAdjustmentDao.changDiffCheck(pd);
                } else {
                    payrollAdjustmentDao.changDiffLinkCheck(pd);
                }
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
            if (Tools.notEmpty(msg)) {
                objResult.setMessage(msg + "为暂存");
            } else {
                objResult.setMessage("差额计算完成");
            }
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }


    public ObjResult findChecked(PageData pd) {
        ObjResult objResult;
        try {
            PageData zb = payrollAdjustmentDao.findZbById(pd);
            int summaryCode = new Integer(zb.getString("summaryCode"));
            pd.putAll(zb);
            List<PageData> valueList = new ArrayList<>();
            List<PageData> childs = orgzationDao.findchildbyid(zb.getString("unit"));
            int fillingType = zb.getInteger("fillingType");
            for (PageData child : childs) {
                List<PageData> childlist = new ArrayList<>();
                if (Tools.isEmpty(pd.getString("orgId"))) {
                    if (zb.getString("unit").equals(child.getString("id"))) {
                        childlist = findlist(child.getString("id"), zb.getString("year"), null, summaryCode + 1, fillingType);
                        List<PageData> parentList = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode - 1, fillingType);
                        for (PageData p : childlist) {
                            if ("6".equals(p.getString("fhTableTypeCode")) || ("1".equals(p.getString("fhTableTypeCode")) && parentList.size() > 0)) {
                                p.put("isSmallSummary", 1);
                            }
                        }
                    } else {
                        if ("1".equals(child.getString("typeCode")) || "2".equals(child.getString("typeCode"))) {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "0", summaryCode + 1, fillingType);
                        } else if (!"3".equals(child.getString("typeCode"))) {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode + 1, fillingType);
                        }
                    }
//                            childlist = findlist(child.getString("id"), zb.getString("year"), "0", "1" );
                } else {
                    if (zb.getString("unit").equals(child.getString("id"))) {
                        childlist = findlist(child.getString("id"), zb.getString("year"), null, summaryCode + 1, null, pd.getString("orgId"), fillingType);
                        List<PageData> parentList = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode - 1, null, pd.getString("orgId"), fillingType);
                        for (PageData p : childlist) {
                            if ("6".equals(p.getString("fhTableTypeCode")) || ("1".equals(p.getString("fhTableTypeCode")) && parentList.size() > 0)) {
                                p.put("isSmallSummary", 1);
                            }
                        }
                    } else {
                        if ("1".equals(child.getString("typeCode")) || "2".equals(child.getString("typeCode"))) {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "0", summaryCode + 1, null, pd.getString("orgId"), fillingType);
                        } else if (!"3".equals(child.getString("typeCode"))) {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode + 1, fillingType);
                        }

                    }
                }
                for (PageData pdd : childlist) {
                    valueList.add(pdd);
                }
            }

            pd.put("childList", valueList);
            objResult = new ObjResult(CommonCode.SUCCESS);
            objResult.setPageData(pd);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    public ObjResult findDiffChecked(PageData pd) {
        ObjResult objResult;
        try {
            PageData zb = payrollAdjustmentDao.findZbById(pd);
            int fillingType = zb.getInteger("fillingType");
            int summaryCode = new Integer(zb.getString("summaryCode"));
            pd.putAll(zb);
            List<PageData> valueList = new ArrayList<>();
            List<PageData> childlist_copy = new ArrayList<>();
            List<PageData> childs = orgzationDao.findchildbyid(zb.getString("unit"));
            for (PageData child : childs) {
                List<PageData> childlist = new ArrayList<>();
                if (Tools.isEmpty(pd.getString("orgId"))) {
                    if (zb.getString("unit").equals(child.getString("id"))) {
                        childlist = findlist(child.getString("id"), zb.getString("year"), null, summaryCode, null, null, "1", fillingType);
                        childlist_copy.addAll(childlist);
                        for (PageData p : childlist_copy) {
                            if (p.getString("id").equals(zb.getString("id"))) {
                                childlist.remove(p);
                            } else if ("6".equals(p.getString("fhTableTypeCode")) && p.getString("summaryCode").equals(summaryCode - 1 + "")) {
                                childlist.remove(p);
                            } else if ("6".equals(p.getString("fhTableTypeCode")) && p.getString("summaryCode").equals(summaryCode + "")) {
                                p.put("isSmallSummary", 1);
                            }
                        }
                    } else {
                        if ("1".equals(child.getString("typeCode")) || "2".equals(child.getString("typeCode"))) {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "0", summaryCode, null, null, "1", fillingType);
                        } else {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode, null, null, "1", fillingType);
                        }
                    }
//                            childlist = findlist(child.getString("id"), zb.getString("year"), "0", "1" );
                } else {
                    if (zb.getString("unit").equals(child.getString("id"))) {
                        childlist = findlist(child.getString("id"), zb.getString("year"), null, summaryCode, null, pd.getString("orgId"), "1", fillingType);
                        childlist_copy.addAll(childlist);
                        for (PageData p : childlist_copy) {
                            if (p.getString("id").equals(zb.getString("id"))) {
                                childlist.remove(p);
                            } else if ("6".equals(p.getString("fhTableTypeCode")) && p.getString("summaryCode").equals(summaryCode - 1 + "")) {
                                childlist.remove(p);
                            } else if ("6".equals(p.getString("fhTableTypeCode")) && p.getString("summaryCode").equals(summaryCode + "")) {
                                p.put("isSmallSummary", 1);
                            }
                        }
                    } else {
                        if ("1".equals(child.getString("typeCode")) || "2".equals(child.getString("typeCode"))) {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "0", summaryCode, null, pd.getString("orgId"), "1", fillingType);
                        } else {
                            childlist = findlist(child.getString("id"), zb.getString("year"), "6", summaryCode, null, pd.getString("orgId"), "1", fillingType);

                        }

                    }
                }
                for (PageData pdd : childlist) {
                    valueList.add(pdd);
                }
            }

            pd.put("childList", valueList);
            objResult = new ObjResult(CommonCode.SUCCESS);
            objResult.setPageData(pd);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /**
     * 汇总表勾选状态
     */
    public ObjResult findfm(PageData pd) {
        ObjResult objResult;
        try {
            PageData zb = payrollAdjustmentDao.findZbById(pd);
            PageData propertyPd = new PageData();
            propertyPd.put("orgId", TokenUtil.getTokenOrgId());
            propertyPd.put("competentOrganization", TokenUtil.getCompetentOrganization());
            PageData propertyinfo = propertyBasicsDao.findByOrgIdAndCom(propertyPd);
            if (propertyinfo == null) {
                propertyinfo = new PageData();
            }
            propertyinfo.put("orgName", orgzationDao.findbyid(zb.getString("unit")).getString("name"));
//            PageData fm = payrollAdjustmentDao.findfm(pd);
            if (propertyinfo != null) {
                propertyinfo.remove("id");
                pd.put("hzUnitName", propertyinfo.getString("orgName"));
                pd.put("fhCompanyName", propertyinfo.getString("orgName"));
                pd.put("fhCompanyCode", propertyinfo.getString("orgCode"));
                pd.put("fhIndustry", propertyinfo.getString("majorIndustries"));
                pd.put("fhOrganization", propertyinfo.getString("organizationForm"));
            }
//            if (fm != null) {
//                pd.putAll(fm);
//            }
            pd.put("fhTableTypeCode", zb.getString("fhTableTypeCode"));
            pd.put("year", zb.getString("year"));
            objResult = new ObjResult(CommonCode.SUCCESS);
            objResult.setPageData(pd);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /*
     * 查找本年度预算是否有数据
     * */

    public ObjResult ishavedata(PageData pd) {
        ObjResult objResult;
        try {
            String msg = "";
            String msg2 = "";
            pd.put("id", pd.getString("formId"));
            PageData zb = payrollAdjustmentDao.findZbById(pd);
            String year = zb.getString("year");
            pd.put(VERSION, payrollVersionService.getVersion(year));
            List<PageData> l1 = payrollAdjustmentDao.listAllOrg(pd);
            if (Tools.isEmpty(pd.getString("reason"))) {
                for (PageData org : l1) {
                    pd.put("orgId", org.getString("orgId"));
                    List<PageData> valuelist = alist(pd);
                    PageData link = payrollAdjustmentDao.findLinkById(pd);
                    if (valuelist.size() == 0) {
                        if (null != msg && !"".equals(msg)) {
                            msg += ",“" + org.getString("NAME") + "”";
                        } else {
                            msg = "“" + org.getString("NAME") + "”";
                        }
                    }
                    if (link != null && Tools.notEmpty(link.getString("status")) && "-1".equals(link.getString("status"))) {
                        if (null != msg2 && !"".equals(msg2)) {
                            msg2 += ",“" + org.getString("NAME") + "”";
                        } else {
                            msg2 = "“" + org.getString("NAME") + "”";
                        }
                    }
                }
                if (!"".equals(msg)) {
                    objResult = new ObjResult(CommonCode.FAIL);
                    if (Tools.notEmpty(msg2)) {
                        objResult.setMessage(msg2 + "为暂存状态，请先保存后再上报");
                    } else {
                        objResult.setMessage(msg + "未填报，请完成填报后再送审");
                    }
                } else {
                    if (!"1".equals(zb.getString("isPassed"))) {
                        objResult = new ObjResult(CommonCode.FAIL);
                        objResult.setMessage("请先通过全表保存再进行上报");
                    } else {
                        payrollAdjustmentDao.updateReason(pd);
                        objResult = new ObjResult(CommonCode.SUCCESS);
                    }
                }
            } else {
                payrollAdjustmentDao.updateReason(pd);
                objResult = new ObjResult(CommonCode.SUCCESS);
            }


        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
            return objResult;
        }
        return objResult;
    }

    /**
     * 提交单据
     *
     * @param formId 单据id
     */
    public HashMap<String, Object> commit(String formId){
        PageData pageData = new PageData();
        pageData.put("status", String.valueOf(FLOW));
        ArrayList<String> ids = new ArrayList<>();
        ids.add(formId);
        pageData.put("id", ids);
        HashMap<String, Object> hs = new HashMap<>();
        try {
            payrollAdjustmentDao.refer(pageData);
            pageData.put("id", formId);
            PageData pd = payrollAdjustmentDao.findZbById(pageData);
            hs.put("reason", pd.getString("reason"));
        }catch (Exception e){
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException("提交失败！");
        }
        return hs;
    }

    /**
     * 退回单据
     *
     * @param formId 单据id
     */
    public Boolean reject(String formId, int formState, String comment){
        PageData pageData = new PageData();
        pageData.put("status", String.valueOf(formState));
        pageData.put("id", Collections.singletonList(formId));
        sendMessage(formId,formState,"");
        try {
            payrollAdjustmentDao.refer(pageData);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException("回退失败！");
        }
        return true;
    }

    public Boolean finish(String formId) {
        try {
            PageData pageData = new PageData();
            pageData.put("status", String.valueOf(FINISH));
            pageData.put("id", Collections.singletonList(formId));
            payrollAdjustmentDao.refer(pageData);
            sendMessage(formId,FINISH,"");
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException(new ObjResult("单据流转完成时修改单据状态失败！"));
        }
        return true;
    }


    private Boolean sendMessage(String id, int updateStatus,String comment) {
        try {
            PageData pageData = new PageData();
            pageData.put("id",id);
            PageData payrollAdjustment = payrollAdjustmentDao.findZbById(pageData);
            if (Optional.ofNullable(payrollAdjustment).isPresent()){
                String dataSource = payrollAdjustment.getString("dataSource");
                String dataSourceId = payrollAdjustment.getString("dataSourceId");
                if (StringUtil.isNotBlank(dataSource) &&
                        StringUtil.isNotBlank(dataSourceId)){
                    if (updateStatus == FormStatusConstant.FINISH || updateStatus == FormStatusConstant.REJECT){
                        ApprovalMessage approvalMessage = new ApprovalMessage();
                        approvalMessage.setDataSourceId(dataSourceId);
                        approvalMessage.setOpinion(comment);
                        approvalMessage.setIsApprove(updateStatus == FormStatusConstant.FINISH ? 2 : 1);
                        approvalMessage.setIsSend(1);
                        approvalMessage.setDataSourceType(GZW_TYPE);
                        if (dataSource.equals(YthConstant.DATA_NINGBO)) {
                            approvalMessage.setDataSourceType(YthConstant.DATA_NINGBO);
                            approvalMessage.setFilingUserName(UserCache.userIdToName(TokenUtil.getTokenUserId()));
                            approvalMessage.setFormType(FormTypeConstant.FR_PAYROLL_ADJUSTMENT);
                        }
                        approvalMessageService.save(approvalMessage);
                    }
                }
            }
            return true;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            return false;
        }
    }

    /**
     * @param formIds 单据ids
     */
    public List<PageData> findByIds(List<String> formIds) {
        try {
            PageData pageData = new PageData();
            pageData.put("ids", formIds);
            List<PageData> byIds = payrollAdjustmentDao.findByIds(pageData);
            List<PageData> byIds2 = new ArrayList<>();
            for (PageData byid:byIds){
                String unitname = byid.getString("UNITNAME");
                byid.put("orgName",unitname);
                byIds2.add(byid);
            }
            return byIds2;
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            throw new MyRuntimeException(new ObjResult("根据单据id列表查询报表失败！"));
        }
    }

    @Override
    public List<Integer> getFormType() {
        return Arrays.asList(FR_PAYROLL_ADJUSTMENT);
    }

    /**
     * 导出报表
     *
     * @param
     * @throws Exception
     */
    public void exportExcel(ArrayList<LinkedHashMap> pd, String year, HttpServletResponse response, OutputStream os) throws Exception {
//
//        PageData pd = new PageData(request);
        String fname = "2024年度市属企业工资预算表";
        os = response.getOutputStream();//取得输出流
        response.reset();//清空输出流
        //下面是对中文文件名的处理
        response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式
        //读取系统中的模板
        String  fileName = "2024年度市属企业工资预算表.xlsx";
        InputStream inputStream = this.getClass().getResourceAsStream("/exceldemo/" + fileName);
        fname = java.net.URLEncoder.encode(fname, "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname.getBytes("UTF-8"), "UTF-8") + ".xls");

        response.setContentType("application/msexcel");//定义输出类型

        String tableNames[] = new String[pd.size()];
        int t = 0;
//        获取报表额orgId
        for (LinkedHashMap pdd : pd) {
            PageData p = new PageData();
            tableNames[t] = pdd.keySet().toString().substring(1, pdd.keySet().toString().length() - 1);
            t++;
        }

//        克隆模板得到一个新的excel模板
        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(inputStream, tableNames);
//        获取工作簿
        Workbook workbook = dynamicOperateExcelUtils.getWorkbook();
//        遍历所有表，将每张表的数据塞入表中
        String message = "";
        for (int i = 0; i < pd.size(); i++) {
//            获取表格
            Sheet sheet = dynamicOperateExcelUtils.getSheet(i);
//           获取对应的表格数据
            LinkedHashMap lp = pd.get(i);
            PageData p = new PageData();
            p.putAll(lp);
//            获得前端传过来的每行的数据存入list
            List<List<String>> valueList = (List<List<String>>) p.get("data");
//            可变行报表的插入方式
            List<List<String>> addList = (List<List<String>>) p.get("map");

//            获取当前表的所有列

            List<PageData> columnList = payrollAdjustmentDao.findcolumn(p);
            String columns[] = new String[columnList.size()];
            int j = 0;
            for (PageData col : columnList) {
                columns[j] = "c" + j + "," + col.getString("isNumValue");
                j++;
            }
//            插入数据
            String type = payrollAdjustmentDao.findorgname(p).getString("ischange");
            for (int a = 0; a < valueList.size(); a++) {
//                可变行报表的增行
                if (a > 0 && addList != null && addList.size() > 0 && (addList.get(a).get(0) == addList.get(a - 1).get(0))) {
                    try {
                        dynamicOperateExcelUtils.appendRows(a, valueList.get(a), columns);
                    } catch (Exception e) {
                        if (Tools.isEmpty(message)) {
                            message += payrollAdjustmentDao.findorgname(p).getString("name");
                        } else {
                            message += "," + payrollAdjustmentDao.findorgname(p).getString("name");
                        }
                        break;
                    }

                } else {
//                    非增行的替换模板单元格的数据
                    for (int b = 0; b < columnList.size(); b++) {
                        try {
                            dynamicOperateExcelUtils.replaceCellValue(a, b, valueList.get(a).get(b));
                        } catch (Exception e) {
                            if (Tools.isEmpty(message)) {
                                message += payrollAdjustmentDao.findorgname(p).getString("name");
                            } else {
                                message += "," + payrollAdjustmentDao.findorgname(p).getString("name");
                            }
                            continue;
                        }
                    }

                }

            }
            if ("ta_13".equals(p.getString("orgId")) || "ta_14".equals(p.getString("orgId"))) {
                for (int a = 0; a < valueList.size(); a++) {
                    if (a == 5 || a == 6 || ("ta_14".equals(p.getString("orgId")) && a > 6 && (addList.get(a).get(0) != addList.get(a - 1).get(0)))) {
                        for (int b = 0; b < columnList.size(); b++) {
                            try {

                                dynamicOperateExcelUtils.replaceCellValue(a, b, valueList.get(a).get(b));

                            } catch (Exception e) {
                                if (Tools.isEmpty(message)) {
                                    message += payrollAdjustmentDao.findorgname(p).getString("name");
                                } else {
                                    message += "," + payrollAdjustmentDao.findorgname(p).getString("name");
                                }
                                continue;
                            }
                        }
                    }
                }
            }
//            替换sheet的名称
            workbook.setSheetName(i, payrollAdjustmentDao.findorgname(p).getString("name"));
//            强制执行公式
            sheet.setForceFormulaRecalculation(true);
        }

//        dynamicOperateExcelUtils.exportExcel("D:/excel2.xlsx");
        workbook.write(os);
        os.flush();
        os.close();
        workbook.close();
    }

    /**
     * 导入报表
     *
     * @param
     * @throws Exception
     */
    public ObjResult uploadFile(MultipartHttpServletRequest request) {
        ObjResult objResult;
        try {
//            按表格的标签名称导入
            PageData pd = new PageData(request);
            if (pd.containsKey("year")){
                pd.remove("year");
            }
            MultipartFile mp = request.getFile("file");
            String year =  request.getParameterMap().get("year")[0];
            if (mp != null) {
                InputStream stream = mp.getInputStream();
                //接收文件流
                //将流给工作簿
                Workbook book = null;
                try {
                    book = new HSSFWorkbook(stream);
                } catch (Exception ex) {
                    // 解决read error异常
                    stream = mp.getInputStream();
                    book = new XSSFWorkbook(stream);
                }
                //遍历sheet
                String version = payrollVersionService.getVersion(year);
                for (int i = 0; i < book.getNumberOfSheets(); i++) {
                    List<PageData> list = new ArrayList<>();
                    Sheet sheet = book.getSheetAt(i);
                    String sheetName = sheet.getSheetName();
                    PageData orgPd = new PageData();
                    orgPd.put("sheetName", sheetName);
                    orgPd.put("version", version);
                    PageData orgpd = payrollAdjustmentDao.findOrgByName(orgPd);
                    //遍历行
                    for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                        Row row = sheet.getRow(rowNum);
                        PageData pd_row = new PageData();
                        if (null == row) {
                            continue;
                        }
                        //遍历列
                        for (int cellNum = 0; cellNum <= row.getLastCellNum(); cellNum++) {
                            Cell cell = row.getCell(cellNum);
                            if (null == cell) {
                                continue;
                            }

                            if (orgpd == null) {
                                pd_row.put("c" + cellNum, getvalue(cell));
                            } else {
                                pd_row.put("c" + cellNum, getvalue(cell, orgpd.getString("isChange")));
                            }

                        }
                        list.add(pd_row);
                    }

                    pd.put(sheet.getSheetName(), list);
                }
                book.close();
                stream.close();
            }
            objResult = new ObjResult(CommonCode.SUCCESS);
            objResult.setPageData(pd);

        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
            objResult = new ObjResult(CommonCode.ERROR);
        }
        return objResult;
    }

    //    private  boolean checkStrIsnum(String s){
//        Pattern pattern = Pattern.compile("-?[0-9]+(\\.[0-9]+)?");
//        Matcher isNum = pattern.matcher(s);
//        if (!isNum.matches()) {
//            return false;
//        }
//        return true;
//    }
    //获取单元格值
    public String getvalue(Cell cell) {
        return getvalue(cell, null);
    }

    public String getvalue(Cell cell, String type) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
//        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
//            cell.setCellType(CellType.STRING);
//        }
        //判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: //数字
                if (isCellDateFormatted(cell)) {
                    short format = cell.getCellStyle().getDataFormat();
                    SimpleDateFormat sdf = null;
                    //					if(format == 14 || format == 31 || format == 57 || format == 58){
                    //日期
                    sdf = new SimpleDateFormat("yyyy年MM月dd日");
                    double value = cell.getNumericCellValue();
                    Date date = DateUtil.getJavaDate(value);
                    cellValue = sdf.format(date);
                } else {
                    //    科学计数法解决数值转String的精度问题
                    cellValue = String.valueOf(getRealStringValueOfDouble(cell.getNumericCellValue()));
                    cell.setCellType(CellType.STRING);

                }
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式

                if (type != null && "0".equals(type)) {
                    try {
                        cellValue = String.valueOf(getRealStringValueOfDouble(cell.getNumericCellValue()));
                        cell.setCellType(CellType.STRING);
                    } catch (IllegalStateException e) {
                        try {
                            cellValue = String.valueOf(cell.getStringCellValue());
                        } catch (IllegalStateException el) {
                            cellValue = "=" + String.valueOf(cell.getCellFormula());

                        }

                    }
                } else {
                    cellValue = "=" + String.valueOf(cell.getCellFormula());
                }
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    //    科学计数法解决数值转String的精度问题
    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean b = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (b) {
            int indexOfE = doubleStr.indexOf('E');
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
                    + BigInteger.ONE.intValue(), indexOfE));
            int pow = Integer.valueOf(doubleStr.substring(indexOfE
                    + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
            Matcher m = PATTERN.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }


    public static boolean isCellDateFormatted(Cell cell) {
        if (cell == null) {
            return false;
        }
        boolean bDate = false;

        double d = cell.getNumericCellValue();
        if (isValidExcelDate(d)) {
            CellStyle style = cell.getCellStyle();
            if (style == null) {
                return false;
            }
            int i = style.getDataFormat();
            String f = style.getDataFormatString();
            bDate = isADateFormat(i, f);
        }
        return bDate;
    }

    public static boolean isADateFormat(int formatIndex, String formatString) {
        if (isInternalDateFormat(formatIndex)) {
            return true;
        }

        if ((formatString == null) || (formatString.length() == 0)) {
            return false;
        }

        String fs = formatString;
        //下面这一行是自己手动添加的 以支持汉字格式wingzing
        fs = fs.replaceAll("[\"|\']", "").replaceAll("[年|月|日|时|分|秒|毫秒|微秒]", "");

        fs = fs.replaceAll("\\\\-", "-");

        fs = fs.replaceAll("\\\\,", ",");

        fs = fs.replaceAll("\\\\.", ".");

        fs = fs.replaceAll("\\\\ ", " ");

        fs = fs.replaceAll(";@", "");

        fs = fs.replaceAll("^\\[\\$\\-.*?\\]", "");

        fs = fs.replaceAll("^\\[[a-zA-Z]+\\]", "");

        return (fs.matches("^[yYmMdDhHsS\\-/,. :]+[ampAMP/]*$"));
    }

    public static boolean isInternalDateFormat(int format) {
        switch (format) {
            case 14:
            case 15:
            case 16:
            case 17:
            case 18:
            case 19:
            case 20:
            case 21:
            case 22:
            case 45:
            case 46:
            case 47:
                return true;
            case 23:
            case 24:
            case 25:
            case 26:
            case 27:
            case 28:
            case 29:
            case 30:
            case 31:
                return true;
            case 32:
            case 33:
            case 34:
            case 35:
            case 36:
            case 37:
            case 38:
            case 39:
            case 40:
            case 41:
            case 42:
            case 43:
            case 44:
            default:
                break;
        }
        return false;
    }

    public static boolean isValidExcelDate(double value) {
        return (value > -4.940656458412465E-324D);
    }

    public void goBackToStash(String formId) {
        try {
            PageData pageData = new PageData();
            pageData.put("status", String.valueOf(UNREVIEWED));
            pageData.put("id", Collections.singletonList(formId));
            payrollAdjustmentDao.refer(pageData);
        } catch (Exception e) {
            log.error(ExceptionUtils.getFullStackTrace(e));
        }
    }
}

